import pymysql
import logging
import sys
import configparser
import pandas as pd

# 加入日志
# 获取logger实例
logger = logging.getLogger("baseSpider")
# 指定输出格式
formatter = logging.Formatter('%(asctime)s\
              %(levelname)-8s:%(message)s')
# 文件日志
file_handler = logging.FileHandler("operation_database.log")
file_handler.setFormatter(formatter)
# 控制台日志
console_handler = logging.StreamHandler(sys.stdout)
console_handler.setFormatter(formatter)

# 为logge添加具体的日志处理器
logger.addHandler(file_handler)
logger.addHandler(console_handler)

logger.setLevel(logging.INFO)


class db_helper:

    def __init__(self, sql, params=None):
        self.sql = sql
        self.params = params
        self.conn: pymysql.Connection = None
        self.cur: pymysql.cursors.Cursor = None

    # 连接数据库
    def connection_database(self):
        conf = configparser.ConfigParser()
        conf.read('static/config.ini')
        url = conf.get('database', 'url')
        username = conf.get('database', 'username')
        password = conf.get('database', 'password')
        database_name = conf.get('database', 'database_name')
        try:
            self.conn = pymysql.connect(url, username, password, database_name)
        except:
            logging.error("connection database failed")
            return False
        self.cur = self.conn.cursor()
        return True

    # 关闭数据库
    def close(self):
        # 如果数据打开，则关闭；否则没有操作
        if self.conn and self.cur:
            self.cur.close()
            self.conn.close()
        return True

    # 执行数据库的SQL语句，主要用来做插入操作
    def execute(self):
        self.connection_database()
        try:
            if self.conn and self.cur:
                # 正常逻辑，执行SQL，提交操作
                self.cur.execute(self.sql, self.params)
                self.conn.commit()
        except:
            logger.error("execute failed: " + self.sql)
            logger.error("params: " + self.params)
            self.close()
            return False
        return True

    # 查询数据
    def select(self):
        self.connection_database()
        self.cur.execute(self.sql, self.params)
        col = self.cur.description
        df = pd.read_sql(sql=self.sql, con=self.conn)
        result_list = []
        df_len = len(df)
        for i in range(df_len):
            result = {}
            for c in col:
                field: str = c[0]
                result[field] = df.get(field)[i]
            result_list.append(result)
        return result_list
